﻿CREATE TRIGGER [dbo].[triu_PaymentInput]
ON [dbo].[t_PaymentInput]
AFTER INSERT,UPDATE,DELETE AS

DECLARE @RC int SET @RC = @@ROWCOUNT
IF @RC = 0 RETURN
IF @RC > 1 BEGIN RAISERROR('Payment Input does not support batched transactions.',16,1) ROLLBACK RETURN END
IF TRIGGER_NESTLEVEL(@@PROCID) > 1 RETURN
SET NOCOUNT ON;

IF EXISTS(SELECT * FROM inserted WHERE Amount < ISNULL(PrincipalPart,0)+ISNULL(InterestPart,0)+ISNULL(LateFeePart,0)+ISNULL(NSFFeePart,0)) BEGIN
	ROLLBACK TRAN
	RAISERROR('[Payment Aamount] must be greater or equial then [Principal Prt]+[Inerest Part]+[Late Fee Part] + [NSF Fee Part].',16,1)
	RETURN
END

IF NOT EXISTS(SELECT * FROM deleted)BEGIN -- insert
	INSERT INTO t_Payment(LoanID,StatusID)SELECT LoanID,StatusID FROM inserted
	UPDATE PI SET PaymentID = SCOPE_IDENTITY() FROM t_PaymentInput PI INNER JOIN inserted i ON PI.ID = i.ID
END ELSE IF NOT EXISTS(SELECT * FROM inserted) -- delete
	DELETE P FROM t_Payment P
	INNER JOIN deleted d ON P.ID = d.PaymentID
ELSE -- update
	UPDATE P SET IsProcessed = 1 FROM t_Payment P INNER JOIN inserted i ON P.ID = i.PaymentID

DECLARE @InsDel TABLE(LoanID int,DatePaid datetime,ID int)
INSERT INTO @InsDel
SELECT ISNULL(i.LoanID,d.LoanID)LoanID,dbo.MinDate(d.DatePaid,i.DatePaid)DatePaid,ISNULL(i.ID,d.ID) ID FROM deleted d FULL OUTER JOIN inserted i ON 1=1


DECLARE @PIIDs TABLE(ID int,DatePaid datetime)
INSERT INTO @PIIDs
SELECT PINP.ID,PINP.DatePaid FROM t_PaymentInput PINP 
--INNER JOIN t_Payment P ON P.ID = PINP.PaymentID
INNER JOIN @InsDel di
	ON PINP.LoanID = di.LoanID AND (PINP.DatePaid>di.DatePaid OR PINP.DatePaid = di.DatePaid AND PINP.ID >= di.ID)

DELETE PT FROM @PIIDs di
INNER JOIN t_PaymentInput PINP ON PINP.ID = di.ID
INNER JOIN t_PaymentTransaction PT ON PT.PaymentID = PINP.PaymentID

DECLARE @PIID int
DECLARE IDsCursor CURSOR READ_ONLY FOR SELECT ID FROM @PIIDs PINP ORDER BY PINP.DatePaid ,PINP.ID

OPEN IDsCursor

EXECUTE AS Login = dbo.Config_SystemLogin()

FETCH NEXT FROM IDsCursor INTO @PIID
WHILE (@@fetch_status = 0)BEGIN
	PRINT @PIID
	EXEC s_PaymentInput_Process @PIID
	FETCH NEXT FROM IDsCursor INTO @PIID
END

REVERT

CLOSE IDsCursor
DEALLOCATE IDsCursor
--BEGIN TRAN
----DELETE t_PaymentInput WHERE ID = 109
--UPDATE t_PaymentInput SET DatePaid = '5/2/05' WHERE ID = 150
----UPDATE t_PaymentInput SET DatePaid = '5/2/05' WHERE ID = 150
--SELECT P.PaymentBalance FROM v_Payment P WHERE LoanID = 1051216 ORDER BY ID
--ROLLBACK
